﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Collections;
using System.Data.SqlClient;

namespace IT_Tracker.DADOS
{
    public class FastExport
    {       
        Hashtable myHashtable;
        public void ExportToExcel_Ticket(DataSet dataSet, string outputPath, string usuario, string dataInicial, string dataFinal)
        {

            // Create the Excel Application object
            //ApplicationClass excelApp = new ApplicationClass();
            Microsoft.Office.Interop.Excel.Application excelApp = new Application();

            // Create a new Excel Workbook
            Workbooks workbooks = excelApp.Workbooks;
            Workbook excelWorkbook = workbooks.Add(Type.Missing);
            
            int sheetIndex = 0;

            // Copy each DataTable
            foreach (System.Data.DataTable dt in dataSet.Tables)
            {
                // Copy the DataTable to an object array
                object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];

                // Copy the column names to the first row of the object array
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    rawData[7, col] = dt.Columns[col].ColumnName;       // Inicia a copia a partir da 10ª Linha
                }

                // Copy the values to the object array
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    for (int row = 7; row < dt.Rows.Count; row++)
                    {
                        if (Convert.ToString(dt.Rows[row].ItemArray[col]) == "01/01/1900 00:00:00")  //Verificar se o valor da data é 01/01/1900
                        {
                            rawData[row + 1, col] = " - ";
                        }
                        else
                        {
                            rawData[row + 1, col] = dt.Rows[row].ItemArray[col];                        
                        }                        
                    }
                }

                //Montando Cabeçalho do Relatório
                rawData[1, 1] = "Controle Ticket Olos";
                rawData[2, 1] = "Criado Por: ";
                rawData[2, 3] = usuario;
                rawData[3, 1] = "Data de Criação: ";
                rawData[3, 3] = DateTime.Now.ToString("dd/MM/yyyy HH:mm");
                rawData[4, 1] = "Data Inicial: ";
                rawData[4, 3] = dataInicial;
                rawData[5, 1] = "Data Final: ";
                rawData[5, 3] = dataFinal;

                // Calculate the final column letter
                string finalColLetter = string.Empty;
                string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
                int colCharsetLen = colCharset.Length;

                if (dt.Columns.Count > colCharsetLen)
                {
                    finalColLetter = colCharset.Substring(
                        (dt.Columns.Count - 1) / colCharsetLen - 1, 1);
                }

                finalColLetter += colCharset.Substring(
                        (dt.Columns.Count - 1) % colCharsetLen, 1);

                // Create a new Sheet
                Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(
                    excelWorkbook.Sheets.get_Item(++sheetIndex),
                    Type.Missing, 1, XlSheetType.xlWorksheet);

                excelSheet.Name = dt.TableName;

                // Fast data export to Excel
                string excelRange = string.Format("A1:{0}{1}",
                    finalColLetter, dt.Rows.Count + 1);

                excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;

                // Formatando Cabeçalho
                ((Range)excelSheet.Rows[8, Type.Missing]).Font.Bold = true;
                ((Range)excelSheet.Rows[8, Type.Missing]).Interior.Color = XlRgbColor.rgbLightSteelBlue;
                ((Range)excelSheet.Rows[8, Type.Missing]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

                // Formatando cores linha a linha
                for (int rows = 7; rows < dt.Rows.Count - 1; rows++)
                {
                    if (rows % 2 != 0)
                    {
                        excelSheet.Rows[rows + 2, Type.Missing].Interior.Color = XlRgbColor.rgbWhiteSmoke;
                    }
                    
                }

                //Formatação do Cabeçalho
                Range sheetRange = excelSheet.get_Range("B2", "E6");                
                sheetRange.Borders[XlBordersIndex.xlEdgeBottom].Color = XlRgbColor.rgbBlack;
                sheetRange.Borders[XlBordersIndex.xlEdgeRight].Color = XlRgbColor.rgbBlack;
                sheetRange.Borders[XlBordersIndex.xlEdgeTop].Color = XlRgbColor.rgbBlack;
                sheetRange.Borders[XlBordersIndex.xlEdgeLeft].Color = XlRgbColor.rgbBlack;
                sheetRange.Borders[XlBordersIndex.xlInsideHorizontal].Color = XlRgbColor.rgbWhiteSmoke;
                sheetRange.Borders[XlBordersIndex.xlInsideVertical].Color = XlRgbColor.rgbWhiteSmoke;
                sheetRange.NumberFormat = "dd/MM/yyyy";

                #region Mesclando celulas
                sheetRange = excelSheet.get_Range("B2", "E2");                
                sheetRange.Cells.Merge();
                sheetRange = excelSheet.get_Range("B3", "C3");
                sheetRange.Cells.Merge();
                sheetRange = excelSheet.get_Range("B4", "C4");
                sheetRange.Cells.Merge();
                sheetRange = excelSheet.get_Range("B5", "C5");
                sheetRange.Cells.Merge();
                sheetRange = excelSheet.get_Range("B6", "C6");
                sheetRange.Cells.Merge();
                                
                sheetRange = excelSheet.get_Range("D3", "E3");
                sheetRange.Cells.Merge();
                sheetRange = excelSheet.get_Range("D4", "E4");
                sheetRange.Cells.Merge();
                sheetRange = excelSheet.get_Range("D5", "E5");
                sheetRange.Cells.Merge();
                sheetRange = excelSheet.get_Range("D6", "E6");
                sheetRange.Cells.Merge();

                #endregion



                // Formatação Geral da Planilha
                excelApp.ActiveWindow.DisplayGridlines = false;
                excelSheet.Columns.AutoFit();
                excelSheet.Rows.Resize.RowHeight = 15;
                ((Range)excelSheet.Cells[Type.Missing, 11]).Resize.ColumnWidth = 14;
                ((Range)excelSheet.Cells[Type.Missing, 6]).Resize.ColumnWidth = 87;
                ((Range)excelSheet.Cells[Type.Missing, 8]).EntireColumn.NumberFormat = "dd-MM-yyyy hh:mm";
                ((Range)excelSheet.Cells[Type.Missing, 9]).EntireColumn.NumberFormat = "dd-MM-yyyy";
                ((Range)excelSheet.Cells[Type.Missing, 10]).EntireColumn.NumberFormat = "dd-MM-yyyy hh:mm";

                Marshal.ReleaseComObject(excelSheet);

            }

            // Salvar e Fechar o WorkBook
            excelWorkbook.Application.DisplayAlerts = false;  // não exibir alertas (inclusive de compatibilidade)
            excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            excelWorkbook.Close(true, Type.Missing, Type.Missing);
            
            //liberando COM Objects
            Marshal.ReleaseComObject(excelWorkbook);
            Marshal.ReleaseComObject(excelApp);
            Marshal.ReleaseComObject(workbooks);

            workbooks = null;
            excelWorkbook = null;

            // Liberar o Objeto Aplicação
            excelApp.Quit();
            excelApp = null;

            
            // Collect the unreferenced objects
            GC.Collect();
            GC.WaitForPendingFinalizers();
            
        }

        public void CheckExcellProcesses()
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            myHashtable = new Hashtable();
            int iCount = 0;

            foreach (Process ExcelProcess in AllProcesses)
            {
                myHashtable.Add(ExcelProcess.Id, iCount);
                iCount = iCount + 1;
            }
        }

        public void KillExcel()
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");

            // check to kill the right process
            foreach (Process ExcelProcess in AllProcesses)
            {
                if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
                    ExcelProcess.Kill();
            }

            AllProcesses = null;
        }

    }
    
}
